package net.wantongtech.xa.uploadbasicinfo.dao;

import com.google.common.collect.Lists;
import com.wtkj.vo.dzzf.base.qxsjz.entity.IntegratedTransOut;
import com.wtkj.vo.dzzf.base.qxsjz.entity.PortalTransOut;
import net.wantongtech.xa.uploadbasicinfo.vo.migrate.MigrateDataLog;
import net.wantongtech.xa.uploadbasicinfo.vo.migrate.TabPartition;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * tr_portal_transout数据迁移DAO实现
 *
 * @author MaShibo
 * 202j/3/j0 00j0
 */
@Repository
public class MigrateDataDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    private JdbcTemplate secondJdbcTemplate;


    private LobHandler lobHandler = new DefaultLobHandler();

    public List<TabPartition> queryTabPartitions(String tableName) {
        final String sql = "SELECT a.partition_name, a.high_value, a.partition_position\n" +
                "  FROM dba_tab_partitions a\n" +
                " WHERE a.table_name = ?\n" +
                " ORDER BY a.partition_position";
        return jdbcTemplate.query(sql, rs -> {
            List<TabPartition> tabPartitionList = Lists.newArrayList();
            while (rs.next()) {
                TabPartition tabPartition = new TabPartition();
                tabPartition.setPartitionName(rs.getString(1));
                tabPartition.setHighValue(lobHandler.getClobAsString(rs, 2));
                tabPartition.setPartitionPosition((Integer) JdbcUtils.getResultSetValue(rs, 3, Integer.class));
                tabPartitionList.add(tabPartition);
            }
            return tabPartitionList;
        }, tableName);
    }

    public List<MigrateDataLog> queryMigrateDataLog(Integer type) {
        final String sql = " SELECT * FROM tmp_migrate_log a WHERE a.type = ? ORDER BY a.belongdate";
        return secondJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(MigrateDataLog.class),
                type);
    }

    public Integer queryPartitionSize(String tableName, String partitionName) {
        final String sql = String.format("select count(1) from %s partition(%s)", tableName, partitionName);
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    public List<PortalTransOut> queryPortalTransOutByPartition(String partitionName, int start, int end) {
        final String sql =
                String.format("SELECT *" +
                        "  FROM (SELECT a.*,rownum AS rm" +
                        "          FROM tr_portal_transout PARTITION(%s) a" +
                        "         WHERE rownum <= ?)" +
                        " WHERE rm >= ?", partitionName);
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(PortalTransOut.class), end, start);
    }

    public List<IntegratedTransOut> queryIntegratedTransOutByPartition(String partitionName, int start, int end) {
        final String sql =
                String.format("SELECT *" +
                        "  FROM (SELECT a.*, rownum AS rm" +
                        "          FROM tr_integrated_transout PARTITION(%s) a" +
                        "         WHERE rownum <= ?)" +
                        " WHERE rm >= ?", partitionName);
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(IntegratedTransOut.class), end, start);
    }


    public Boolean existsTargetTable(String tableName) {
        final String sql =
                "SELECT *" +
                        "  FROM all_all_tables a" +
                        " WHERE a.owner = upper(?)" +
                        "   AND a.table_name = upper(?)";
        return secondJdbcTemplate.query(sql, ResultSet::next, "zwuser", tableName);
    }

    public void alterTableNoLogging(String tableName) {
        final String sql = String.format("alter table zwuser.%s nologging", tableName);
        jdbcTemplate.execute(sql);
    }

    public void alterTableLogging(String tableName) {
        final String sql = String.format("alter table zwuser.%s logging", tableName);
        jdbcTemplate.execute(sql);
    }


    public void executeSql(String sql) {
        secondJdbcTemplate.execute(sql);
    }


    public int[] batchInsertPortalTransOut(String tableName, List<PortalTransOut> portalTransOutList) {

        final String baseSql =
                "INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(%s(transactionid)) */" +
                        "INTO zwuser.%s" +
                        "  (transactionid," +
                        "   tollprovinceid," +
                        "   issuerid," +
                        "   messageid," +
                        "   sectionid," +
                        "   tollgrantryid," +
                        "   payfee," +
                        "   fee," +
                        "   discountfee," +
                        "   obusign," +
                        "   obuid," +
                        "   etccardtype," +
                        "   etccardid," +
                        "   entolllaneid," +
                        "   entime," +
                        "   entollstationname," +
                        "   extollstationname," +
                        "   transtime," +
                        "   vehicleplate," +
                        "   vehiclecolor," +
                        "   identifyvehicleplate," +
                        "   identifyvehiclecolor," +
                        "   vehicletype," +
                        "   identifyvehicletype," +
                        "   vehicleclass," +
                        "   transfee," +
                        "   servicetype," +
                        "   vehiclesignid," +
                        "   description," +
                        "   passid," +
                        "   obumac," +
                        "   obusn," +
                        "   direction," +
                        "   signstatus," +
                        "   manualsign," +
                        "   link," +
                        "   vehiclesignids," +
                        "   enpassrecordid," +
                        "   forwardetcrecordid," +
                        "   backwardetcrecordid," +
                        "   mediatype," +
                        "   mediano," +
                        "   extolllaneid," +
                        "   envehicleplate," +
                        "   envehiclecolor," +
                        "   envehicletype," +
                        "   paytype," +
                        "   identification," +
                        "   specialtype," +
                        "   TYPE," +
                        "   createtime," +
                        "   accountid," +
                        "   privilegetype," +
                        "   fundaccountname," +
                        "   channeltype," +
                        "   signsource," +
                        "   cleardate," +
                        "   clearflag," +
                        "   balanceon," +
                        "   state," +
                        "   integratedid," +
                        "   isintegrated," +
                        "   subserviceid," +
                        "   discounttype," +
                        "   provincediscountfee," +
                        "   originfee," +
                        "   vehiclesign," +
                        "   channelpushstate," +
                        "   OPEN," +
                        "   expandname," +
                        "   feemileage," +
                        "   shortfee," +
                        "   shortfeemileage," +
                        "   cardtotalamount," +
                        "   obutotalamount," +
                        "   obutotaldiscountamount," +
                        "   obuprovincefee," +
                        "   prebalance," +
                        "   postbalance," +
                        "   transpaytype," +
                        "   inprovince," +
                        "   processtime," +
                        "   accountreceivetime)" +
                        "VALUES" +
                        " (?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?," +
                        "  ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?, " +
                        "  ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,  " +
                        "  ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?, " +
                        "  ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?, " +
                        "  ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?, " +
                        "  ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?,    ?)";
        String sql = String.format(baseSql, tableName, tableName);
        return secondJdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                PortalTransOut pt = portalTransOutList.get(i);
                int index = 1;
                ps.setObject(index++, pt.getTransactionId());
                ps.setObject(index++, pt.getTollProvinceId());
                ps.setObject(index++, pt.getIssuerId());
                ps.setObject(index++, pt.getMessageId());
                ps.setObject(index++, pt.getSectionId());
                ps.setObject(index++, pt.getTollGranTryId());
                ps.setObject(index++, pt.getPayFee());
                ps.setObject(index++, pt.getFee());
                ps.setObject(index++, pt.getDiscountFee());
                ps.setObject(index++, pt.getObuSign());
                ps.setObject(index++, pt.getObuId());
                ps.setObject(index++, pt.getEtcCardType());
                ps.setObject(index++, pt.getEtcCardId());
                ps.setObject(index++, pt.getEnTollLaneId());
                ps.setObject(index++, pt.getEnTime());
                ps.setObject(index++, pt.getEnTollStationName());
                ps.setObject(index++, pt.getExTollStationName());
                ps.setObject(index++, pt.getTransTime());
                ps.setObject(index++, pt.getVehiclePlate());
                ps.setObject(index++, pt.getVehicleColor());
                ps.setObject(index++, pt.getIdentifyVehiclePlate());
                ps.setObject(index++, pt.getIdentifyVehicleColor());
                ps.setObject(index++, pt.getVehicleType());
                ps.setObject(index++, pt.getIdentifyVehicleType());
                ps.setObject(index++, pt.getVehicleClass());
                ps.setObject(index++, pt.getTransFee());
                ps.setObject(index++, pt.getServiceType());
                ps.setObject(index++, pt.getVehicleSignId());
                ps.setObject(index++, pt.getDescription());
                ps.setObject(index++, pt.getPassId());
                ps.setObject(index++, pt.getObuMac());
                ps.setObject(index++, pt.getObuSn());
                ps.setObject(index++, pt.getDirection());
                ps.setObject(index++, pt.getSignStatus());
                ps.setObject(index++, pt.getManualSign());
                ps.setObject(index++, pt.getLink());
                ps.setObject(index++, pt.getVehicleSignIds());
                ps.setObject(index++, pt.getEnPassRecordId());
                ps.setObject(index++, pt.getForwardETCRecordId());
                ps.setObject(index++, pt.getBackwardETCRecordId());
                ps.setObject(index++, pt.getMediaType());
                ps.setObject(index++, pt.getMediaNo());
                ps.setObject(index++, pt.getExTollLaneId());
                ps.setObject(index++, pt.getEnVehiclePlate());
                ps.setObject(index++, pt.getEnVehicleColor());
                ps.setObject(index++, pt.getEnVehicleType());
                ps.setObject(index++, pt.getPayType());
                ps.setObject(index++, pt.getIdentification());
                ps.setObject(index++, pt.getSpecialType());
                ps.setObject(index++, pt.getType());
                ps.setObject(index++, pt.getCreateTime());
                ps.setObject(index++, pt.getAccountId());
                ps.setObject(index++, pt.getPrivilegeType());
                ps.setObject(index++, pt.getFundAccountName());
                ps.setObject(index++, pt.getChannelType());
                ps.setObject(index++, pt.getSignSource());
                ps.setObject(index++, pt.getClearDate());
                ps.setObject(index++, pt.getClearFlag());
                ps.setObject(index++, pt.getBalanceOn());
                ps.setObject(index++, pt.getState());
                ps.setObject(index++, pt.getIntegratedId());
                ps.setObject(index++, pt.getIsIntegrated());
                ps.setObject(index++, pt.getSubServiceId());
                ps.setObject(index++, pt.getDiscountType());
                ps.setObject(index++, pt.getProvinceDiscountFee());
                ps.setObject(index++, pt.getOriginFee());
                ps.setObject(index++, pt.getVehicleSign());
                ps.setObject(index++, pt.getChannelPushState());
                ps.setObject(index++, pt.getOpen());
                ps.setObject(index++, pt.getExpandName());
                ps.setObject(index++, pt.getFeeMileage());
                ps.setObject(index++, pt.getShortFee());
                ps.setObject(index++, pt.getShortFeeMileage());
                ps.setObject(index++, pt.getCardTotalAmount());
                ps.setObject(index++, pt.getObuTotalAmount());
                ps.setObject(index++, pt.getObuTotaldisCountAmount());
                ps.setObject(index++, pt.getObuProvinceFee());
                ps.setObject(index++, pt.getPreBalance());
                ps.setObject(index++, pt.getPostBalance());
                ps.setObject(index++, pt.getTransPayType());
                ps.setObject(index++, pt.getInProvince());
                ps.setObject(index++, pt.getProcessTime());
                ps.setObject(index, pt.getAccountReceiveTime());
            }

            @Override
            public int getBatchSize() {
                return portalTransOutList.size();
            }
        });
    }

    public void batchInsertIntegratedTransOut(String tableName, List<IntegratedTransOut> integratedTransOutList) {
        final String baseSql =
                "INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(%s(INTEGRATEDID)) */ " +
                        " INTO zwuser.%s" +
                        "  (INTEGRATEDID," +
                        "   PASSID," +
                        "   COUNT," +
                        "   FEE," +
                        "   EXTIME," +
                        "   ENTIME," +
                        "   CARDID," +
                        "   OBUID," +
                        "   VEHICLEPLATE," +
                        "   VEHICLECOLOR," +
                        "   ENTOLLSTATIONNAME," +
                        "   EXTOLLSTATIONNAME," +
                        "   TOLLPROVINCEID," +
                        "   SERVICETYPE," +
                        "   OBUSIGN," +
                        "   USERID," +
                        "   USERIDNUM," +
                        "   USERIDTYPE," +
                        "   USERNAME," +
                        "   DESCRIPTION," +
                        "   SIGNSOURCE," +
                        "   CHANNELTYPE," +
                        "   CREATETIME," +
                        "   STATE," +
                        "   MESSAGEID," +
                        "   SENDLISTID," +
                        "   INPROVINCE," +
                        "   EXPANDNAME," +
                        "   OPEN," +
                        "   ENPROVINCEID) " +
                        "VALUES " +
                        "  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
                        "   ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
                        "   ?, ?, ?, ?)";
        String sql = String.format(baseSql, tableName, tableName);
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                IntegratedTransOut integratedTransOut = integratedTransOutList.get(i);
                int index = 1;
                ps.setObject(index++, integratedTransOut.getIntegratedId());
                ps.setObject(index++, integratedTransOut.getPassId());
                ps.setObject(index++, integratedTransOut.getCount());
                ps.setObject(index++, integratedTransOut.getFee());
                ps.setObject(index++, integratedTransOut.getExTime());
                ps.setObject(index++, integratedTransOut.getEnTime());
                ps.setObject(index++, integratedTransOut.getCardId());
                ps.setObject(index++, integratedTransOut.getObuId());
                ps.setObject(index++, integratedTransOut.getVehiclePlate());
                ps.setObject(index++, integratedTransOut.getVehicleColor());
                ps.setObject(index++, integratedTransOut.getEnTollStationName());
                ps.setObject(index++, integratedTransOut.getExTollStationName());
                ps.setObject(index++, integratedTransOut.getTollProvinceId());
                ps.setObject(index++, integratedTransOut.getServiceType());
                ps.setObject(index++, integratedTransOut.getObuSign());
                ps.setObject(index++, integratedTransOut.getUserId());
                ps.setObject(index++, integratedTransOut.getUserIdNum());
                ps.setObject(index++, integratedTransOut.getUserIdType());
                ps.setObject(index++, integratedTransOut.getUserName());
                ps.setObject(index++, integratedTransOut.getDescription());
                ps.setObject(index++, integratedTransOut.getSignSource());
                ps.setObject(index++, integratedTransOut.getChannelType());
                ps.setObject(index++, integratedTransOut.getCreateTime());
                ps.setObject(index++, integratedTransOut.getState());
                ps.setObject(index++, integratedTransOut.getMessageId());
                ps.setObject(index++, integratedTransOut.getSendListId());
                ps.setObject(index++, integratedTransOut.getInProvince());
                ps.setObject(index++, integratedTransOut.getExpandName());
                ps.setObject(index++, integratedTransOut.getOpen());
                ps.setObject(index, integratedTransOut.getEnProvinceId());
            }

            @Override
            public int getBatchSize() {
                return integratedTransOutList.size();
            }
        });

    }

}
